package com.app.util.db.tool;

import java.sql.Connection;
import java.sql.SQLException;

import com.app.util.array.ArrayUtil;
import com.app.util.db.DBPool;
import com.app.util.db.bean.TableDefined;
import com.app.util.db.util.SqlNameTranser;
import com.app.util.db.util.TableDefinedUtil;
import com.app.util.object.FieldUtil;
import com.app.util.pool.NewObjectException;
import com.app.util.string.StringUtil;
import com.app.util.string.StringUtil.ArrayConverter;

public class Update extends SQLShell {

	private TableUID tableUid;

	public Update(DBPool dbPool) {
		super(dbPool);
		tableUid = new TableUID(dbPool);
	}

	/**
	 * 插入对象为记录
	 * 
	 * @param table
	 *            表名
	 * @param obj
	 *            对象
	 * @return 
	 * @throws SQLException
	 */
	public int insert(Object obj) throws SQLException {
		TableDefined tableDefined = TableDefinedUtil.ask(obj.getClass());
		if (tableDefined.useTableUID()) {
			if (null == FieldUtil.get(obj, tableDefined.id())) {
				FieldUtil.set(obj, tableDefined.id(), tableUid.next(tableDefined));
			}
		}
		Connection con = null;
		try {
			String[] proNames = FieldUtil.propertys(obj.getClass());
			String[] standardFields = StringUtil.convert2StringArray(proNames, new ArrayConverter() {
				public String convert(Object source) {
					return SqlNameTranser.IT.toString(source);
				}
			});
			Object[] values = new Object[proNames.length];
			for (int i = 0; i < proNames.length; i++) {
				values[i] = FieldUtil.get(obj, proNames[i]);
			}
			con = dbPool.getConnection();
			StringBuffer sqlSb = new StringBuffer();
			sqlSb.append("INSERT INTO ");
			sqlSb.append(SqlNameTranser.IT.toString(tableDefined.table())).append("(").append(StringUtil.linkString(standardFields, ", ")).append(") ");
			sqlSb.append("VALUE").append("(").append(StringUtil.linkString(StringUtil.fillArray(proNames, "?"), ", ")).append(")");
			return dbUtil.update(con, sqlSb.toString(), values);
		} catch (NewObjectException e) {
			throw new SQLException(e);
		} finally {
			dbPool.releaseConnection(con);
		}
	}

	/**
	 * 根据ID更新一条记录
	 * 
	 * @param obj
	 * @return
	 * @throws SQLException
	 */
	public int updateSingle(Object obj) throws SQLException {
		TableDefined tableDefined = TableDefinedUtil.ask(obj.getClass());
		return updateSingle(obj, tableDefined.id());
	}

	/**
	 * 根据唯一键字段更新一条记录
	 * 
	 * @param obj
	 * @param uniqueFieldName
	 * @return
	 * @throws SQLException
	 */
	public int updateSingle(Object obj, String uniqueFieldName) throws SQLException {
		return updateSingle(obj, new String[] { uniqueFieldName });
	}

	/**
	 * 根据联合唯一键字段更新一条记录
	 * 
	 * @param obj
	 * @param uniqueFieldNames
	 * @return
	 * @throws SQLException
	 */
	public int updateSingle(Object obj, String[] uniqueFieldNames) throws SQLException {
		return updateSingle(obj, uniqueFieldNames, null);
	}

	/**
	 * 根据唯一键列表字段条件更新表中的数据（不更新notUpdateFields中的字段）
	 * 
	 * @param table
	 * @param obj
	 * @param uniqueFieldNames
	 * @param notUpdateFields
	 * @return
	 * @throws SQLException
	 */
	public int updateSingle(Object obj, String[] uniqueFieldNames, String[] notUpdateFields) throws SQLException {
		TableDefined tableDefined = TableDefinedUtil.ask(obj.getClass());
		Connection con = null;
		try {
			String[] proNames = FieldUtil.propertys(obj.getClass());

			proNames = ArrayUtil.removeItem(proNames, uniqueFieldNames);
			proNames = ArrayUtil.removeItem(proNames, notUpdateFields);

			String[] setList = StringUtil.convert2StringArray(proNames, new ArrayConverter() {
				public String convert(Object source) {
					return SqlNameTranser.IT.toString(source) + "=?";
				}
			});
			Object[] values = new Object[proNames.length + uniqueFieldNames.length];
			for (int i = 0; i < proNames.length; i++) {
				values[i] = FieldUtil.get(obj, proNames[i]);
			}
			for (int i = 0; i < uniqueFieldNames.length; i++) {
				values[values.length - uniqueFieldNames.length + i] = FieldUtil.get(obj, uniqueFieldNames[i]);
			}
			con = dbPool.getConnection();
			StringBuffer sqlSb = new StringBuffer();
			sqlSb.append("UPDATE ");
			sqlSb.append(SqlNameTranser.IT.toString(tableDefined.table())).append(" SET ").append(StringUtil.linkString(setList, ", "));
			sqlSb.append(" WHERE 1=1");

			for (int i = 0; i < uniqueFieldNames.length; i++) {
				sqlSb.append(" AND ").append(SqlNameTranser.IT.toString(uniqueFieldNames[i])).append("=?");
			}
			sqlSb.append(" LIMIT 1");

			return dbUtil.update(con, sqlSb.toString(), values);
		} catch (NewObjectException e) {
			throw new SQLException(e);
		} finally {
			dbPool.releaseConnection(con);
		}
	}

	/**
	 * 更新数据操作
	 * 
	 * @param sql
	 *            SQL语句
	 * @return
	 * @throws SQLException
	 */
	public int update(String sql) throws SQLException {
		Connection con = null;
		try {
			con = dbPool.getConnection();
			return dbUtil.update(con, sql);
		} catch (NewObjectException e) {
			throw new SQLException(e);
		} finally {
			dbPool.releaseConnection(con);
		}
	}

	/**
	 * 更新数据操作，带参数
	 * 
	 * @param sql
	 *            SQL语句
	 * @param params
	 *            参数组
	 * @return
	 * @throws SQLException
	 */
	public int update(String sql, Object[] params) throws SQLException {
		Connection con = null;
		try {
			con = dbPool.getConnection();
			return dbUtil.update(con, sql, params);
		} catch (NewObjectException e) {
			throw new SQLException(e);
		} finally {
			dbPool.releaseConnection(con);
		}
	}

	/**
	 * 根据ID删除一条记录
	 * 
	 * @param obj
	 * @return
	 * @throws SQLException
	 */
	public int deleteSingle(Object obj) throws SQLException {
		TableDefined tableDefined = TableDefinedUtil.ask(obj.getClass());
		return deleteSingle(obj, tableDefined.id());
	}

	/**
	 * 根据唯一键字段删除一条记录
	 * 
	 * @param obj
	 * @param uniqueFieldName
	 * @return
	 * @throws SQLException
	 */
	public int deleteSingle(Object obj, String uniqueFieldName) throws SQLException {
		return deleteSingle(obj, new String[] { uniqueFieldName });
	}

	/**
	 * 根据唯一键字段删除一条记录
	 * 
	 * @param table
	 *            表名
	 * @param obj
	 *            数据对象
	 * @param idFieldName
	 *            唯一键字段
	 * @return 影响记录数
	 * @throws SQLException
	 */
	public int deleteSingle(Object obj, String[] uniqueFieldNames) throws SQLException {
		TableDefined tableDefined = TableDefinedUtil.ask(obj.getClass());
		Connection con = null;
		try {
			StringBuffer sqlSb = new StringBuffer("DELETE FROM ").append(SqlNameTranser.IT.toString(tableDefined.table()));
			sqlSb.append(" WHERE ");
			for (int i = 0; i < uniqueFieldNames.length; i++) {
				if (i > 0) {
					sqlSb.append(" AND ");
				}
				sqlSb.append(SqlNameTranser.IT.toString(uniqueFieldNames[i])).append("=?");
			}
			sqlSb.append(" LIMIT 1");
			Object[] params = new Object[uniqueFieldNames.length];
			for (int i = 0; i < uniqueFieldNames.length; i++) {
				params[i] = FieldUtil.get(obj, uniqueFieldNames[i]);
			}
			con = dbPool.getConnection();
			return dbUtil.update(con, sqlSb.toString(), params);
		} catch (NewObjectException e) {
			throw new SQLException(e);
		} finally {
			dbPool.releaseConnection(con);
		}
	}
}
